﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;

public class DbHelper : IDisposable
{
    #region Declarations

    public enum TransactionType : uint
    {
        Open = 1,
        Commit = 2,
        Rollback = 3
    }

    private static readonly string ConnectionString =
        ConfigurationManager.ConnectionStrings["StampControllerConnection"].ConnectionString;

    private static readonly string DataProvider = ConfigurationManager.AppSettings["DataProvider"];

    private DbCommand _dbCommand;
    private DbConnection _dbConnection;
    private DbProviderFactory _dbProviderFactory;
    private DbTransaction _dbTransaction;
    private bool _isTransaction;

    #region Nested type: DbCommands

    private class DbCommands : IDisposable
    {
        private readonly DbCommand _dbCommand;
        private bool _hasOutputParameter;

        public DbCommands(DbCommand dbCommand)
        {
            _dbCommand = dbCommand;
        }

        #region Destructors

        private bool _isDisposed;

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        private void Dispose(bool isDisposing)
        {
            if (!_isDisposed)
            {
                if (isDisposing)
                {
                    //Clean Up managed resources
                    if (!_hasOutputParameter) _dbCommand.Parameters.Clear();
                }
                //Clean up unmanaged resources
            }

            _isDisposed = true;
        }

        ~DbCommands()
        {
            Dispose(false);
        }

        #endregion

        /// <summary>
        ///     This function is used to Prepare Command For Execution
        ///     Has to be changed/removed if object based array concept is removed.
        /// </summary>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        private void PrepareCommand(string cmdText,
            CommandType cmdType = CommandType.Text)
        {
            if (_dbCommand != null)
            {
                _dbCommand.CommandText = cmdText;
                _dbCommand.CommandType = cmdType;
            }
        }

        public void PrepareCommand(string cmdText, object[,] cmdParms = null,
            CommandType cmdType = CommandType.Text)
        {
            PrepareCommand(cmdText, cmdType);

            if (null != cmdParms)
                CreateDbParameters(cmdParms);
        }

        /// <summary>
        ///     This function is used to Prepare Command For Execution
        ///     Has to be changed/removed if object based array concept is removed.
        /// </summary>
        /// <param name="cmdParms"> </param>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        public void PrepareCommand(string cmdText, IEnumerable<Parameters> cmdParms,
            CommandType cmdType = CommandType.Text)
        {
            PrepareCommand(cmdText, cmdType);

            if (null != cmdParms)
                CreateDbParameters(cmdParms);
        }

        /// <summary>
        ///     This function is used to Create Parameters for the Command For Execution
        /// </summary>
        private void CreateDbParameters(object[,] parameters)
        {
            for (int i = 0; i < parameters.Length/2; i++)
            {
                DbParameter dbParameter = _dbCommand.CreateParameter();
                dbParameter.ParameterName = parameters[i, 0].ToString();
                dbParameter.Value = parameters[i, 1] ?? DBNull.Value;
                _dbCommand.Parameters.Add(dbParameter);
            }
        }

        /// <summary>
        ///     This function is used to Create Parameters for the Command For Execution
        /// </summary>
        private void CreateDbParameters(IEnumerable<Parameters> colParameters)
        {
            foreach (Parameters oParam in colParameters)
            {
                DbParameter dbParameter = _dbCommand.CreateParameter();
                dbParameter.ParameterName = oParam.ParamName;
                dbParameter.Value = oParam.ParamValue ?? DBNull.Value;
                dbParameter.Direction = oParam.ParamDirection;
                if (!_hasOutputParameter && oParam.ParamDirection == ParameterDirection.Output)
                    _hasOutputParameter = true;
                _dbCommand.Parameters.Add(dbParameter);
            }
        }
    }

    #endregion

    #region Nested type: Parameters

    /// <summary>
    ///     Parameters class
    /// </summary>
    public struct Parameters
    {
        public ParameterDirection ParamDirection;
        public string ParamName;
        public object ParamValue;

        public Parameters(string name, object value, ParameterDirection direction)
        {
            ParamName = name;
            ParamValue = value;
            ParamDirection = direction;
        }

        public Parameters(string name, object value)
        {
            ParamName = name;
            ParamValue = value;
            ParamDirection = ParameterDirection.Input;
        }
    }

    #endregion

    #endregion

    #region Construtors & Destructors

    private bool _isDisposed;

    public DbHelper()
    {
        _dbProviderFactory = DbProviderFactories.GetFactory(DataProvider);

        EstablishFactoryConnection();

        if (null == _dbCommand)
        {
            _dbCommand = _dbProviderFactory.CreateCommand();
            if (_dbCommand != null)
            {
                _dbCommand.Connection = _dbConnection;
                _dbCommand.CommandTimeout = 200;
            }
        }
    }

    public void Dispose()
    {
        Dispose(true);
        GC.SuppressFinalize(this);
    }

    protected void Dispose(bool isDisposing)
    {
        if (!_isDisposed)
        {
            if (isDisposing)
            {
                //Clean Up managed resources

                // Dispose command
                _dbCommand.Dispose();
                _dbCommand = null;

                // Dispose connection
                CloseFactoryConnection();

                // Dispose factory
                _dbProviderFactory = null;
            }
            //Clean up unmanaged resources
        }

        _isDisposed = true;
    }

    ~DbHelper()
    {
        Dispose(false);
    }

    #endregion

    #region Connection Methods

    /// <summary>
    ///     This function is used to Open Database Connection.
    /// </summary>
    private void EstablishFactoryConnection()
    {
        // This check is not required as it will throw "Invalid Provider Exception" on the contructor itself.
        if (0 == DbProviderFactories.GetFactoryClasses().Select("InvariantName='" + DataProvider + "'").Length)
            throw new Exception("Invalid Provider");

        _dbConnection = _dbProviderFactory.CreateConnection();

        if (_dbConnection != null && _dbConnection.State == ConnectionState.Closed)
        {
            _dbConnection.ConnectionString = ConnectionString;
            _dbConnection.Open();
        }
    }

    /// <summary>
    ///     This function is used to Close Database Connection
    /// </summary>
    private void CloseFactoryConnection()
    {
        // Check for an open connection            
        try
        {
            if (_dbConnection.State == ConnectionState.Open)
            {
                _dbConnection.Close();
            }
        }
        catch (DbException ex)
        {
            //catch any SQL server data provider generated error messag
            throw new Exception(ex.Message);
        }
        catch (NullReferenceException ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            if (null != _dbConnection)
                _dbConnection.Dispose();

            _dbConnection = null;
        }
    }

    /// <summary>
    ///     This function is used to Handle Transaction Events
    /// </summary>
    /// <param name="transactionType">Transaction Event Type</param>
    public void TransactionHandler(TransactionType transactionType)
    {
        switch (transactionType)
        {
            case TransactionType.Open: //open a transaction
                try
                {
                    _dbTransaction = _dbConnection.BeginTransaction();
                    _isTransaction = true;
                }
                catch (InvalidOperationException ex)
                {
                    throw new Exception("@TransactionHandler - " + ex.Message);
                }
                break;

            case TransactionType.Commit: //commit the transaction
                if (null != _dbTransaction.Connection)
                {
                    try
                    {
                        _dbTransaction.Commit();
                        _isTransaction = false;
                    }
                    catch (InvalidOperationException ex)
                    {
                        throw new Exception("@TransactionHandler - " + ex.Message);
                    }
                }
                break;

            case TransactionType.Rollback: //rollback the transaction
                try
                {
                    if (_isTransaction)
                    {
                        _dbTransaction.Rollback();
                        _isTransaction = false;
                    }
                }
                catch (InvalidOperationException ex)
                {
                    throw new Exception("@TransactionHandler - " + ex.Message);
                }
                break;
        }
    }

    #endregion

    #region Execute Methods

    /// <summary>
    ///     This function is used to execute the command
    /// </summary>
    /// <param name="cmdText"></param>
    /// <param name="cmdParms"></param>
    /// <returns>Count of records affected</returns>
    public int ExecuteNonQuery(string cmdText, IEnumerable<Parameters> cmdParms = null)
    {
        return ExecuteNonQuery(cmdText, CommandType.Text, cmdParms);
    }

    /// <summary>
    ///     This function is used to execute the command
    /// </summary>
    /// <param name="cmdType"></param>
    /// <param name="cmdText"></param>
    /// <param name="cmdParms"></param>
    /// <returns>Count of records affected</returns>
    public int ExecuteNonQuery(string cmdText, CommandType cmdType, object[,] cmdParms = null)
    {
        try
        {
            int i = 0;
            using (var command = new DbCommands(_dbCommand))
            {
                if (_isTransaction) _dbCommand.Transaction = _dbTransaction;
                command.PrepareCommand(cmdText, cmdParms, cmdType);
                i = _dbCommand.ExecuteNonQuery();
            }

            return i;
        }
        catch (DbException)
        {
            throw;
        }
        catch
        {
            throw;
        }
    }

    /// <summary>
    ///     This function is used to execute the command
    /// </summary>
    /// <param name="cmdType"></param>
    /// <param name="cmdText"></param>
    /// <param name="cmdParms"></param>
    /// <returns>Count of records affected</returns>
    public int ExecuteNonQuery(string cmdText, CommandType cmdType, IEnumerable<Parameters> cmdParms)
    {
        try
        {
            int i = 0;
            using (var command = new DbCommands(_dbCommand))
            {
                if (_isTransaction) _dbCommand.Transaction = _dbTransaction;
                command.PrepareCommand(cmdText, cmdParms, cmdType);
                i = _dbCommand.ExecuteNonQuery();
            }

            return i;
        }
        catch (DbException)
        {
            throw;
        }
        catch
        {
            throw;
        }
    }

    #endregion

    #region DataReader Methods

    /// <summary>
    ///     This function is used to fetch data using data reader
    /// </summary>
    /// <param name="cmdText"></param>
    /// <param name="cmdParms"> </param>
    /// <returns>DbDataReader</returns>
    public DbDataReader ExecuteReader(string cmdText, object[,] cmdParms = null)
    {
        return ExecuteReader(cmdText, CommandType.Text, cmdParms);
    }

    /// <summary>
    ///     This function is used to fetch data using data reader
    /// </summary>
    /// <param name="cmdText"></param>
    /// <param name="cmdParms"> </param>
    /// <returns>DbDataReader</returns>
    public DbDataReader ExecuteReader(string cmdText, IEnumerable<Parameters> cmdParms)
    {
        return ExecuteReader(cmdText, CommandType.Text, cmdParms);
    }

    /// <summary>
    ///     This function is used to fetch data using data reader
    /// </summary>
    /// <param name="cmdType"></param>
    /// <param name="cmdText"></param>
    /// <param name="cmdParms"> </param>
    /// <returns>DbDataReader</returns>
    public DbDataReader ExecuteReader(string cmdText, CommandType cmdType, object[,] cmdParms)
    {
        // we use a try/catch here because if the method throws an exception we want to 
        // close the connection throw code, because no datareader will exist, hence the 
        // commandBehaviour.CloseConnection will not work
        try
        {
            DbDataReader dr;
            using (var command = new DbCommands(_dbCommand))
            {
                if (_isTransaction) _dbCommand.Transaction = _dbTransaction;
                command.PrepareCommand(cmdText, cmdParms, cmdType);
                dr = _dbCommand.ExecuteReader();
            }
            return dr;
        }
        catch (DbException)
        {
            throw;
        }
        catch
        {
            throw;
        }
    }

    /// <summary>
    ///     This function is used to fetch data using data reader
    /// </summary>
    /// <param name="cmdType"></param>
    /// <param name="cmdText"></param>
    /// <param name="cmdParms"> </param>
    /// <returns>DbDataReader</returns>
    public DbDataReader ExecuteReader(string cmdText, CommandType cmdType, IEnumerable<Parameters> cmdParms)
    {
        // we use a try/catch here because if the method throws an exception we want to 
        // close the connection throw code, because no datareader will exist, hence the 
        // commandBehaviour.CloseConnection will not work
        try
        {
            DbDataReader dr;
            using (var command = new DbCommands(_dbCommand))
            {
                if (_isTransaction) _dbCommand.Transaction = _dbTransaction;
                command.PrepareCommand(cmdText, cmdParms, cmdType);
                dr = _dbCommand.ExecuteReader();
            }
            return dr;
        }
        catch (DbException)
        {
            throw;
        }
        catch
        {
            throw;
        }
    }

    #endregion

    #region DataAdapter Methods

    /// <summary>
    ///     This function is used to fetch data using data adapter
    ///     Has to be changed/removed if object based array concept is removed.
    /// </summary>
    /// <param name="cmdText"></param>
    /// <param name="cmdParms"></param>
    /// <returns>DataSet</returns>
    public DataSet GetDataSet(string cmdText, object[,] cmdParms = null)
    {
        return GetDataSet(cmdText, CommandType.Text, cmdParms);
    }


    /// <summary>
    ///     This function is used to fetch data using data adapter
    ///     Has to be changed/removed if object based array concept is removed.
    /// </summary>
    /// <param name="cmdType"></param>
    /// <param name="cmdText"></param>
    /// <param name="cmdParms"></param>
    /// <returns>DataSet</returns>
    public DataSet GetDataSet(string cmdText, CommandType cmdType, object[,] cmdParms = null)
    {
        // we use a try/catch here because if the method throws an exception we want to 
        // close the connection throw code, because no datareader will exist, hence the 
        // commandBehaviour.CloseConnection will not work
        var ds = new DataSet();
        try
        {
            using (var command = new DbCommands(_dbCommand))
            {
                if (_isTransaction) _dbCommand.Transaction = _dbTransaction;
                command.PrepareCommand(cmdText, cmdParms, cmdType);

                using (DbDataAdapter adapter = _dbProviderFactory.CreateDataAdapter())
                {
                    if (adapter != null)
                    {
                        adapter.SelectCommand = _dbCommand;
                        adapter.Fill(ds);
                    }
                }
            }
            return ds;
        }
        catch (DbException)
        {
            throw;
        }
        catch
        {
            throw;
        }
    }

    /// <summary>
    ///     This function is used to fetch data using data adapter
    ///     Has to be changed/removed if object based array concept is removed.
    /// </summary>
    /// <param name="cmdType"></param>
    /// <param name="cmdText"></param>
    /// <param name="cmdParms"></param>
    /// <returns>DataSet</returns>
    public DataSet GetDataSet(string cmdText, CommandType cmdType, IEnumerable<Parameters> cmdParms)
    {
        // we use a try/catch here because if the method throws an exception we want to 
        // close the connection throw code, because no datareader will exist, hence the 
        // commandBehaviour.CloseConnection will not work
        var ds = new DataSet();
        try
        {
            using (var command = new DbCommands(_dbCommand))
            {
                if (_isTransaction) _dbCommand.Transaction = _dbTransaction;
                command.PrepareCommand(cmdText, cmdParms, cmdType);

                using (DbDataAdapter adapter = _dbProviderFactory.CreateDataAdapter())
                {
                    if (adapter != null)
                    {
                        adapter.SelectCommand = _dbCommand;
                        adapter.Fill(ds);
                    }
                }
            }
            return ds;
        }
        catch (DbException)
        {
            throw;
        }
        catch
        {
            throw;
        }
    }

    public DataTable GetDataTable(string cmdText, object[,] cmdParms = null)
    {
        return GetDataTable(cmdText, CommandType.Text, cmdParms);
    }

    /// <summary>
    ///     This function is used to fetch data using data adapter
    ///     Has to be changed/removed if object based array concept is removed.
    /// </summary>
    /// <param name="cmdType"></param>
    /// <param name="cmdText"></param>
    /// <param name="cmdParms"></param>
    /// <returns>DataTable</returns>
    public DataTable GetDataTable(string cmdText, CommandType cmdType, object[,] cmdParms = null)
    {
        // we use a try/catch here because if the method throws an exception we want to 
        // close the connection throw code, because no datareader will exist, hence the 
        // commandBehaviour.CloseConnection will not work
        var dt = new DataTable();
        try
        {
            using (var command = new DbCommands(_dbCommand))
            {
                if (_isTransaction) _dbCommand.Transaction = _dbTransaction;
                command.PrepareCommand(cmdText, cmdParms, cmdType);

                using (DbDataAdapter adapter = _dbProviderFactory.CreateDataAdapter())
                {
                    if (adapter != null)
                    {
                        adapter.SelectCommand = _dbCommand;
                        adapter.Fill(dt);
                    }
                }
            }
            return dt;
        }
        catch (DbException)
        {
            throw;
        }
        catch
        {
            throw;
        }
    }

    /// <summary>
    ///     This function is used to fetch data using data adapter
    ///     Has to be changed/removed if object based array concept is removed.
    /// </summary>
    /// <param name="cmdType"></param>
    /// <param name="cmdText"></param>
    /// <param name="cmdParms"></param>
    /// <returns>DataTable</returns>
    public DataTable GetDataTable(string cmdText, CommandType cmdType, IEnumerable<Parameters> cmdParms)
    {
        // we use a try/catch here because if the method throws an exception we want to 
        // close the connection throw code, because no datareader will exist, hence the 
        // commandBehaviour.CloseConnection will not work
        var dt = new DataTable();
        try
        {
            using (var command = new DbCommands(_dbCommand))
            {
                if (_isTransaction) _dbCommand.Transaction = _dbTransaction;
                command.PrepareCommand(cmdText, cmdParms, cmdType);

                using (DbDataAdapter adapter = _dbProviderFactory.CreateDataAdapter())
                {
                    if (adapter != null)
                    {
                        adapter.SelectCommand = _dbCommand;
                        adapter.Fill(dt);
                    }
                }
            }
            return dt;
        }
        catch (DbException)
        {
            throw;
        }
        catch
        {
            throw;
        }
    }

    #endregion

    #region Scalar Methods

    /// <summary>
    ///     This function is used to fetch data using data reader
    /// </summary>
    /// <param name="cmdText"></param>
    /// <param name="cmdParms"> </param>
    /// <returns>DbDataReader</returns>
    public T ExecuteScalar<T>(string cmdText, object[,] cmdParms = null)
    {
        return ExecuteScalar<T>(cmdText, CommandType.Text, cmdParms);
    }

    /// <summary>
    ///     This function is used to invoke execute scalar method
    /// </summary>
    /// <param name="cmdType"></param>
    /// <param name="cmdText"></param>
    /// <param name="cmdParms"></param>
    /// <returns>Object</returns>
    public T ExecuteScalar<T>(string cmdText, CommandType cmdType, object[,] cmdParms = null)
    {
        try
        {
            T returnValue;
            using (var command = new DbCommands(_dbCommand))
            {
                if (_isTransaction) _dbCommand.Transaction = _dbTransaction;
                command.PrepareCommand(cmdText, cmdParms, cmdType);

                object obj = _dbCommand.ExecuteScalar();
                if (obj == DBNull.Value) returnValue = default(T);
                else returnValue = (T) (obj ?? default(T));
            }
            return returnValue;
        }
        catch (DbException)
        {
            throw;
        }
        catch
        {
            throw;
        }
    }

    /// <summary>
    ///     This function is used to invoke execute scalar method
    /// </summary>
    /// <param name="cmdType"></param>
    /// <param name="cmdText"></param>
    /// <param name="cmdParms"></param>
    /// <returns>Object</returns>
    public T ExecuteScalar<T>(string cmdText, CommandType cmdType, IEnumerable<Parameters> cmdParms)
    {
        try
        {
            T returnValue;
            using (var command = new DbCommands(_dbCommand))
            {
                if (_isTransaction) _dbCommand.Transaction = _dbTransaction;
                command.PrepareCommand(cmdText, cmdParms, cmdType);

                object obj = _dbCommand.ExecuteScalar();
                if (obj == DBNull.Value) returnValue = default(T);
                else returnValue = (T) (obj ?? default(T));
            }
            return returnValue;
        }
        catch (DbException)
        {
            throw;
        }
        catch
        {
            throw;
        }
    }

    #endregion

    public object this[string key]
    {
        get { return _dbCommand.Parameters[key].Value; }
    }

    public void MappingTable(string cmdText, DataTable table)
    {
        try
        {
            using (var command = new DbCommands(_dbCommand))
            {
                if (_isTransaction) _dbCommand.Transaction = _dbTransaction;
                command.PrepareCommand(cmdText);

                using (DbDataAdapter adapter = _dbProviderFactory.CreateDataAdapter())
                {
                    if (adapter != null)
                    {
                        adapter.SelectCommand = _dbCommand;
                        DbCommandBuilder commandBuilder = _dbProviderFactory.CreateCommandBuilder();
                        if (commandBuilder != null)
                        {
                            commandBuilder.DataAdapter = adapter;
                            adapter.TableMappings.Add(table.TableName, table.TableName);
                            adapter.Update(table);
                        }
                    }
                }
            }
        }
        catch (DbException)
        {
            throw;
        }
        catch
        {
            throw;
        }
    }
}